package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Agency;
import ModelLayer.DiscountGroup;
import ModelLayer.Guest;
import ModelLayer.Room;

public class DBGuest implements IFDBGuest {
	private Connection con;

	public DBGuest() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Guest getGuest(int guestId, boolean retriveAssociation) {
		String wClause = "  guestId = '" + guestId + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertGuest(Guest guest) {
		int rc = -1;
		Integer discountId = null;
		Integer agencyId = null;
		Integer roomId = null;
		if (guest.getDiscountGroup() != null)
			discountId = guest.getDiscountGroup().getId();
		if (guest.getAgency() != null)
			agencyId = guest.getAgency().getId();
		if (guest.getCurrentRoom() != null)
			roomId = guest.getCurrentRoom().getId();
		String query = "INSERT INTO guest(guestId, passportNr, agency_Id, group_Id, currenRoom_Id) VALUES('"
				+ guest.getID()
				+ "','"
				+ guest.getPassNumber()
				+ "',"
				+ discountId + "," + agencyId + "," + roomId + ")";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Guest is not inserted");
		}
		return rc;
	}

	@Override
	public int updateGuest(Guest guest) {
		Guest guestObj = guest;
		int rc = -1;
		Integer discountId = null;
		Integer agencyId = null;
		Integer roomId = null;
		if (guest.getDiscountGroup() != null)
			discountId = guest.getDiscountGroup().getId();
		if (guest.getAgency() != null)
			agencyId = guest.getAgency().getId();
		if (guest.getCurrentRoom() != null)
			roomId = guest.getCurrentRoom().getId();
		String query = "UPDATE guest SET " + "passportNr = '"
				+ guestObj.getPassNumber() + "'," + "agency_Id = " + agencyId
				+ "," + "group_Id = " + discountId + "," + "currenRoom_Id = "
				+ roomId + "" + " WHERE guestId = '" + guestObj.getID() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in guest db: " + e);
		}
		return rc;
	}

	@Override
	public int updateGuestRoom(int guestId, Room room) {
		int rc = -1;
		Integer roomId = null;
		if (room != null)
			roomId = room.getId();
		String query = "UPDATE guest SET " + "currenRoom_Id = " + roomId + ""
				+ " WHERE guestId = '" + guestId + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in guest current room db: "
					+ e);
		}
		return rc;
	}

	@Override
	public int deleteGuest(int guestId) {
		int rc = -1;
		String query = "DELETE FROM guest WHERE guestId = '" + guestId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in guest db: " + ex);
		}
		return rc;
	}

	@Override
	public ArrayList<Guest> getAllGuest(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public ArrayList<Guest> getGuestForRoom(int roomId,
			boolean retrieveAssociation) {
		return miscWhere("currenRoom_Id = '" + roomId + "'",
				retrieveAssociation);
	}

	private Guest singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Guest guest = new Guest();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				guest = buildGuest(results);
				stmt.close();
				if (retrieveAssociation) {
					if (guest.getAgency() != null) {
						IFDBAgency dbAgency = new DBAgency();
						Agency agency = dbAgency.getAgency((int) guest
								.getAgency().getId(), true);
						guest.setAgency(agency);
						System.out.println("Agency is selected");
					}

					if (guest.getDiscountGroup() != null) {
						IFDBDiscountGroup dbDiscountGroup = new DBDiscountGroup();
						DiscountGroup dg = dbDiscountGroup.getDiscount(guest
								.getDiscountGroup().getId());
						guest.setDiscountGroup(dg);
						System.out.println("DiscountGroup is selected");
					}

					if (guest.getCurrentRoom() != null) {
						IFDBRoom dbRoom = new DBRoom();
						Room room = dbRoom.getRoom(guest.getCurrentRoom()
								.getId(), true);
						guest.setCurrentRoom(room);
						System.out.println("Room is selected");
					}
				}
			} else {
				guest = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return guest;
	}

	private ArrayList<Guest> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Guest> list = new ArrayList<Guest>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Guest guest = new Guest();
				guest = buildGuest(results);
				list.add(guest);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Guest guest : list) {
					if (guest.getAgency() != null) {
						IFDBAgency dbAgency = new DBAgency();
						Agency agency = dbAgency.getAgency((int) guest
								.getAgency().getId(), true);
						guest.setAgency(agency);
						System.out.println("Agency is selected");
					}

					if (guest.getDiscountGroup() != null) {
						IFDBDiscountGroup dbDiscountGroup = new DBDiscountGroup();
						DiscountGroup dg = dbDiscountGroup.getDiscount(guest
								.getDiscountGroup().getId());
						guest.setDiscountGroup(dg);
						System.out.println("DiscountGroup is selected");
					}

					if (guest.getCurrentRoom() != null) {
						IFDBRoom dbRoom = new DBRoom();
						Room room = dbRoom.getRoom(guest.getCurrentRoom()
								.getId(), true);
						guest.setCurrentRoom(room);
						System.out.println("Room is selected");
					}
				}
			}

		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Guest buildGuest(ResultSet results) {
		Guest guest = new Guest();
		try {
			guest.setID(results.getInt("guestId"));
			guest.setPassNumber(results.getString("passportNr"));
			if (results.getInt("group_Id") != 0)
				guest.setDiscountGroup(new DiscountGroup(results
						.getInt("group_Id")));

			if (results.getInt("Agency_Id") != 0)
				guest.setAgency(new Agency(results.getInt("Agency_Id")));

			if (results.getInt("currenRoom_Id") != 0)
				guest.setCurrentRoom(new Room(results.getInt("currenRoom_Id")));

		} catch (Exception e) {
			System.out.println("error in building the guest object");
		}
		return guest;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT guestId, passportNr, agency_Id, group_Id, currenRoom_Id FROM guest";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

}
